<?php
namespace App\Http\Query;

use Illuminate\Support\Facades\DB;
use Log;
use PHPUnit\Framework\Constraint\Count;

/*
 * セール品
 */
class SaleQuery
{

    // セール品マスター取得
    public function getAllSaleInfo()
    {
        $sql = 'SELECT sale_master.sale_id, sale_master.sale_name, goods_master.goods_code, goods_master.goods_name, corp_master.corp_name, category_master.category_name
                FROM sale_master, goods_master, sale_goods_master, corp_master, sale_corp_master,category_master
                WHERE sale_master.sale_id = sale_goods_master.sale_id
                AND sale_goods_master.goods_id = goods_master.goods_id
                AND sale_master.sale_id = sale_corp_master.sale_id
                AND sale_corp_master.corp_id = corp_master.corp_id
                AND goods_master.category_id = category_master.category_id
                AND sale_master.delete_flg = 0
                AND goods_master.delete_flg = 0
                AND sale_goods_master.delete_flg = 0
                AND corp_master.delete_flg = 0
                AND category_master.delete_flg = 0
                AND sale_corp_master.delete_flg = 0';

        return  DB::select($sql);
    }

    public function getAllGoodsInfo($goodsId)
    {
        $sql = 'select * from goods_master where goods_id = ? and delete_flg = 0 ';
        $param = [$goodsId];
        $users = DB::select($sql, $param);
        return $users;
    }

    public function addsetMaster($data)//增
    {
        return DB::table('set_master')->insert($data);
    }

    // セール品削除
    public function deleteSaleMaster($saleId)
    {
        $sql = 'UPDATE sale_master,sale_goods_master,sale_corp_master,sale_condiction
                SET sale_master.delete_flg = 1, sale_goods_master.delete_flg = 1, sale_corp_master.delete_flg = 1, sale_condiction.delete_flg = 1
                WHERE  sale_master.sale_id =?
                AND sale_goods_master.sale_id =?
                AND sale_condiction.sale_id =?
                AND sale_corp_master.sale_id =?';

        $param = [$saleId,$saleId,$saleId,$saleId];

        $delflag = DB::update($sql,  $param);
        return $delflag;

    }

    // セール品修正
    public function selectSaleMasterById($saleId)
    {
        $sql = 'select
                sale_master.sale_id, sale_corp_master.corp_id, sale_goods_master.goods_id, sale_master.sale_name, sale_master.start_date, sale_master.end_date, sale_master.display_flg, concat( sale_master.image_url,sale_master.image_file_name) filesrc,
                goods_master.goods_code, goods_master.goods_name,corp_master.corp_name,sale_condiction.condiction_num,sale_condiction.discount_value
                from sale_master, goods_master,sale_goods_master,corp_master,sale_corp_master,sale_condiction

                where sale_master.sale_id = ?

                and  sale_goods_master.sale_id =  sale_master.sale_id


                and goods_master.goods_id = sale_goods_master.goods_id

                and corp_master.corp_id = sale_corp_master.corp_id

                and sale_corp_master.sale_id =  sale_master.sale_id

                and sale_condiction.sale_id =  sale_master.sale_id';
        $param = [$saleId];
        $allInfo = DB::select($sql,  $param);
        return $allInfo;

    }

    public function selectSaleCondictionById($saleId)
    {
        $sql = 'select * from sale_condiction where delete_flg = 0 and sale_id=?';
        $param = [$saleId];
        $saleCondiction = DB::select($sql,  $param);
        return $saleCondiction;
    }


    // カテゴリ取得
    public function getCategoryInfo()
    {
        $sql = 'select category_id, category_name from category_master where delete_flg = 0 order by updated_at desc';
        $categorys = DB::select($sql);
        return $categorys;
    }
    // 商品取得
    public function getGoodsInfo()
    {
        $sql = 'select goods_id, goods_code, goods_name,category_id from goods_master where delete_flg = 0 order by updated_at desc';
        $goods = DB::select($sql);
        return $goods;

    }

    // 会社取得
    public function getCompanyInfo()
    {
        $sql = 'select corp_id, corp_name from corp_master where delete_flg = 0 order by updated_at desc';
        $company = DB::select($sql);
        return $company;
    }

    // セール名称、期間、トップページ表示、画像を保存する sale_master
    public function saveSaleMaster($saleMasterInfo)
    {

        DB::table('sale_master')->insert($saleMasterInfo);
        $saleId= DB::getPdo()->lastInsertId();

        return $saleId;
    }

    /**
     * 商品IDを保存する sale_goods_master
     * 適用対象を保存する  sale_corp_master
     * セール条件を保存する  sale_condiction
     */
    public function saveSaleGoodsCorpCondiction($saleGoodsInfo, $saleCorpMasterInfo, $saleCondictionInfo,$saleCondictionInfoTwo,$saleCondictionInfoThree, $saleId)
    {
        DB::beginTransaction();
        try {
                DB::table('sale_goods_master')->insert($saleGoodsInfo);
                DB::table('sale_corp_master')->insert($saleCorpMasterInfo);
                $LengthSaleCone = count($saleCondictionInfo)/3;
                $LengthSaleCtwo = count($saleCondictionInfoTwo)/3;
                $LengthSaleCthree = count($saleCondictionInfoThree)/3;
                for ($i = 0; $i < $LengthSaleCone; $i ++) {

                    $condiction_type = $saleCondictionInfo["condiction_type_".$i];
                    $condiction_num = $saleCondictionInfo["condiction_num_".$i];
                    $discount_value = $saleCondictionInfo["discount_value_".$i];

                    $sql = 'INSERT INTO sale_condiction(sale_id, condiction_type, condiction_num, discount_value, delete_flg, created_at, updated_at) VALUES (?,?,?,?,0,now(),now())';
                    $param = [$saleId, $condiction_type, $condiction_num, $discount_value];
                    DB::insert($sql,$param);
                }
                for ($i = 0; $i < $LengthSaleCtwo; $i ++) {

                    $condiction_type = $saleCondictionInfoTwo["condiction_type_".$i];
                    $condiction_num = $saleCondictionInfoTwo["condiction_num_".$i];
                    $discount_value = $saleCondictionInfoTwo["discount_value_".$i];

                    $sql = 'INSERT INTO sale_condiction(sale_id, condiction_type, condiction_num, discount_value, delete_flg, created_at, updated_at) VALUES (?,?,?,?,0,now(),now())';
                    $param = [$saleId, $condiction_type, $condiction_num, $discount_value];
                    DB::insert($sql,$param);
                }
                for ($i = 0; $i < $LengthSaleCthree; $i ++) {

                    $condiction_type = $saleCondictionInfoThree["condiction_type_".$i];
                    $condiction_num = $saleCondictionInfoThree["condiction_num_".$i];
                    $discount_value = $saleCondictionInfoThree["discount_value_".$i];

                    $sql = 'INSERT INTO sale_condiction(sale_id, condiction_type, condiction_num, discount_value, delete_flg, created_at, updated_at) VALUES (?,?,?,?,0,now(),now())';
                    $param = [$saleId, $condiction_type, $condiction_num, $discount_value];
                    DB::insert($sql,$param);
                }

                DB::commit();
        } catch (\Exception $e) {
            DB::rollBack();
            return false;
        }
        return true;

    }

    /**
     * 商品IDを修正する sale_goods_master
     * 適用対象を修正する  sale_corp_master
     * セール条件を修正する  sale_condiction
     */
    public function updateSaleGoodsCorpCondiction($param, $saleCondictionInfo, $saleCondictionUpdate, $saleId)
    {

        DB::beginTransaction();
        try {
                $sql = 'UPDATE sale_master,sale_goods_master,sale_corp_master
                        SET sale_master.sale_name = ?, sale_master.start_date = ?, sale_master.end_date = ?, sale_master.display_flg = ?, sale_master.image_url = ?, sale_master.image_file_name = ?, sale_master.image_extension = ?, sale_goods_master.goods_id = ?, sale_corp_master.corp_id = ?
                        WHERE  sale_master.sale_id =?
                        AND sale_goods_master.sale_id =?
                        AND sale_corp_master.sale_id =?
                        AND sale_master.delete_flg= 0
                        AND sale_goods_master.delete_flg= 0
                        AND sale_corp_master.delete_flg= 0';

                DB::update($sql, $param);

                if(!empty($saleCondictionInfo)){
                     $lengthSale = count($saleCondictionInfo)/4;
                      for ($i = 0; $i < $lengthSale; $i ++) {

                      $condiction_type = $saleCondictionInfo["condiction_type_".$i];
                      $condiction_num = $saleCondictionInfo["condiction_num_".$i];
                      $discount_value = $saleCondictionInfo["discount_value_".$i];
                      $condiction_id = $saleCondictionInfo["condiction_id_".$i];

                      $sql = 'UPDATE sale_condiction SET condiction_num = ?, discount_value = ?
                              WHERE  condiction_type = ?
                              AND sale_id = ?
                              AND condiction_id = ?
                              AND delete_flg= 0';
                      $paramCondiction = [$condiction_num, $discount_value, $condiction_type, $saleId, $condiction_id];
                      DB::update($sql, $paramCondiction);
                   }
                }

                if(!empty($saleCondictionUpdate)){
                    $lengthSaleUpdate = count($saleCondictionUpdate)/3;
                    for ($i = 1; $i <= $lengthSaleUpdate; $i ++) {

                        $condiction_type = $saleCondictionUpdate["condiction_type_".$i];
                        $condiction_num = $saleCondictionUpdate["condiction_num_".$i];
                        $discount_value = $saleCondictionUpdate["discount_value_".$i];

                        $sql = 'INSERT INTO sale_condiction(sale_id, condiction_type, condiction_num, discount_value, delete_flg, created_at, updated_at) VALUES (?,?,?,?,0,now(),now())';

                        $paramCondiction = [$saleId, $condiction_type, $condiction_num, $discount_value];
                        DB::update($sql, $paramCondiction);
                    }
                }

                DB::commit();
        } catch (\Exception $e) {
            DB::rollBack();
            return false;
        }
        return true;
    }

    public function deleteUpdateCondictionJson($saleCondictionId)
    {
        $sql = 'UPDATE sale_condiction  SET  delete_flg =1 WHERE condiction_id = ?';
        $param = [
            $saleCondictionId
        ];
        return  DB::update($sql, $param);
    }

}